VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "CEventHandler2"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'_______________________________________________________________________________________________________________
'|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
'
'   Class Module:   CEventHandler2
'
'    This class module can trap events for any workbook you specify.
'    However, you must locate and open the workbook whose events you
'    want to trap, create an instance of this class in a public variable
'    and set the Book property procedure below to the workbook whose
'    events you want to trap in order to use the class.
'_______________________________________________________________________________________________________________
'|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||


Option Explicit


''' This variable traps all events generated by the workbook specified by the Book property.
Private WithEvents wkbEventSource As Excel.Workbook




''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'____________________________________________________________________________________________________
'====================================================================================================
'   Property: Book
'      This property procedure is used to specify which workbook's
'      events this class module should trap.
'
'====================================================================================================
Public Property Set Book(ByRef wkbNewValue As Excel.Workbook)
    Set wkbEventSource = wkbNewValue
End Property


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' These are Class-specific Events ''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Class_Terminate()
    ''' Destroy the WithEvents variable when the class is destroyed.
    Set wkbEventSource = Nothing
End Sub


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Workbook Events are Trapped in This Section ''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub wkbEventSource_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    MsgBox "The Workbook_BeforeSave Event Fired.", vbInformation, wkbEventSource.Name
End Sub


Private Sub wkbEventSource_NewSheet(ByVal Sh As Object)
    MsgBox "The " & TypeName(Sh) & " " & Sh.Name & " was added.", vbInformation, wkbEventSource.Name
End Sub


Private Sub wkbEventSource_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    MsgBox "The Workbook_SheetBeforeRightClick Event Fired." & vbLf & "Sheet: " & Sh.Name & vbLf & "Range: " & Target.Address, vbInformation, wkbEventSource.Name
    Cancel = True
End Sub


Private Sub wkbEventSource_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "The Workbook_SheetChange Event Fired." & vbLf & "Sheet: " & Sh.Name & vbLf & "Range: " & Target.Address, vbInformation, wkbEventSource.Name
End Sub


Private Sub wkbEventSource_BeforeClose(Cancel As Boolean)
    ''' Be sure to destroy the WithEvents variable before the workbook it references is closed.
    ''' In a real-world application, doing this properly can be a complicated task. For the sake
    ''' of simplicity we'll just do this unconditionally in the BeforClose event.
    Set wkbEventSource = Nothing
End Sub

''' NOTE: You can trap as many additional events as you like.
